package com.mpyy.dao.everyday;

import com.mpyy.pojo.PurchaseStock;
import com.mpyy.pojo.SupplierInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface SupplierDaointer {
    /**
     * 根据供货商名称查询信息
     * @param supName
     * @return
     */
    @Select("SELECT supId,supName,supPeople,supPhone,supAddress FROM supplier_info WHERE CONCAT(supId,supName) LIKE  CONCAT('%',#{supName},'%') ")
    @Result(property = "supId",column = "supId",id = true)
    public List<SupplierInfo> supperlierall(@Param("supName") String supName);

    /**
     * 根据供货商id查询信息
     * @param supId
     * @return
     */
    @Select("SELECT supName,supPeople,supPhone,SUM(purStoPayable) purStoPayable,SUM(purStoOutof) purStoOutof,(SUM(purStoPayable)-SUM(purStoOutof)) differ FROM supplier_info a,purchase_stock b,employee_info c,depot_info d WHERE\n" +
            "\t b.empId=c.empId AND b.depotId=d.depotId AND a.supId=b.supid AND a.supId=#{supId}" +
            "\t ")
    public List<PurchaseStock> alldiffer(String supId);

    /**
     * 根据查询出的供货商信息查询详细详细
     * @param purStoId
     * @return
     */
    @Select("SELECT c.proId,c.proName,b.purStoDescComment,b.purStoDescPrice,b.purStoDescNum,b.purStoDescPrice*b.purStoDescNum amount \n" +
            "FROM purchase_stock a,purchase_stock_desc b,product_info c WHERE a.purStoId=b.purStoId AND b.proId=c.proId AND a.purStoId=#{purStoId}")
    public List<PurchaseStock> alldetailed(String purStoId);

    /**
     * 添加供货商
     * @param supplierInfo
     * @return
     */
    @Insert("INSERT INTO supplier_info (supId,supName,supPeople,supPhone,supAddress) VALUES(#{supId},#{supName},#{supPeople},#{supPhone},#{supAddress})")
    public int supperlierinser(SupplierInfo supplierInfo);

    /**
     * 删除供货商信息
     * @param supId
     * @return
     */
    @Delete("DELETE FROM supplier_info WHERE supId=#{supId}")
    public int supperlierdel(String supId);

    /**
     * 修改供货商信息
     * @param supplierInfo
     * @return
     */
    @Update("UPDATE supplier_info SET supName=#{supName},supPeople=#{supPeople},supPhone=#{supPhone},supAddress=#{supAddress} WHERE supId=#{supId}")
    public int supperlierupdata(SupplierInfo supplierInfo);

    @Select("SELECT a.supName,a.supType,SUM(b.purStoOutof) purStoOutof FROM supplier_info a, purchase_stock b WHERE a.supId=b.supId GROUP BY b.supId")
    public List<SupplierInfo> ghs();

    @Select("SELECT custName,custTypeName,SUM(salOrdPaidIn) salOrdPaidIn FROM customer_info a,customer_type b,sale_order c WHERE a.custTypeId=b.custTypeId AND a.custId=c.custId GROUP BY custName")
    @Results(value = {
            @Result(property = "supName", column = "custName"),
            @Result(property = "supType",column = "custTypeName"),
            @Result(property = "purStoOutof",column = "salOrdPaidIn")
    })
    public List<SupplierInfo> kh();
}
